import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {
	private static Connection con=null;
	public Test(){
		if(con==null){
			con=getConnection() ;
		}
	}
	private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String URL =  "jdbc:oracle:thin:@ (DESCRIPTION = (ADDRESS = (PROTOCOL =TCP)"
		+ "(HOST = 10.1.7.102)(PORT = 1521)) (LOAD_BALANCE = yes)(CONNECT_DATA = (SERVICE_NAME = testdb) ) )";
	private static final String USERNAME = "dataexchange";
	private static final String PASSWORD = "dataexchange123";

	private static Connection getConnection() {
		try {
			Class.forName(DRIVER);
			if (con == null) {
				con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
				con.setAutoCommit(false);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
	public static void main(String[] args) throws SQLException {
		Test t = new Test();
		t.select();
	}
	
	public int makeTaskID() {
		String sql = "select S_dec.Nextval from dual";
		PreparedStatement pstat = null;
		ResultSet rs = null;
		int re=-1;
		try {
			pstat = con.prepareStatement(sql);
			rs = pstat.executeQuery();
			while (rs.next()) {
				re = rs.getInt(1);
				return re;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs!=null){
					System.out.println("rs close");
					rs.close();	
				}
				
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			if (pstat != null)
				try {
					System.out.println("pstar close");
					pstat.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return re;
	}
	
	public void interIntoDept() {
		String sql = "";
		sql = "insert into CHERY " + "(ID,DID,DNAME,CID,CNAME)"
				+ "values(S_dec.Nextval,?,?,?,?)";
		PreparedStatement pstat = null;
		int i = 0;
		try {
			pstat = con.prepareStatement(sql);
			pstat.setString(1, "deptid");// 部门或公司id
			pstat.setString(2, "deptname");// 部门或公司名字
			pstat.setString(3, "123456");// 联系人id
			pstat.setString(4, "name");// 联系人id
			pstat.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public void select() throws SQLException {
		String sql = "SELECT SUPPLIERNAME FROM  CHYIII_DEC_TSUPPLIER";
		PreparedStatement pst = con.prepareStatement(sql);
		ResultSet rs = pst.executeQuery();
		while (rs.next()) {
			System.out.println("SUPPLIERNAME=" + rs.getString(1));
		}
		con.commit();
		pst.close();
		con.close();
	}
}
